package model;

import Bean.Room;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.table.TableModel;

public class RoomManagerModel extends RootModel
{

    private List<Room> ListRoom;
    private Room Room = null;

    public boolean AddRoom(Room Room)
    {
        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("INSERT INTO room ( Name,State,Type) VALUES (?,?,?);");
            prst.setInt(1, Room.getName());
            prst.setString(2, Room.getState());
            prst.setString(3, Room.getTyperoom());
            if (prst.executeUpdate() > 0)
            {

                return true;

            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }
        return false;
    }

    public List<Room> getTyperoom()
    {
        ListRoom = new ArrayList<Room>();
        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("SELECT Type FROM typeroom;");
            ResultSet rs = prst.executeQuery();
            while (rs.next())
            {
                Room = new Room();
                Room.setTyperoom(rs.getString("Type"));
                ListRoom.add(Room);
            }
        }
        catch (SQLException ex)
        {
            System.out.println("Error HireRoomModel.getTyperoom ");
        }
        finally
        {
            ConnectDB.close();
        }
        return ListRoom;
    }

    public boolean addRoomType(Room Room)
    {
        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("INSERT INTO typeroom(Type,Price) VALUES (?,?);");
            prst.setString(1, Room.getTyperoom());
            prst.setInt(2, Room.getCostroom());
            if (prst.executeUpdate() > 0)
            {
                return true;
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }
        return false;
    }

    public TableModel getAllRoomType()
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery(" select * from typeroom group by Type");
            rstm = new ResultSetTableModel(res, 5);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public int getPriceOf(int name)
    {
        ConnectDB.connect();
        int price = 0;
        try
        {
            prst = ConnectDB.getPrst("SELECT Price FROM typeroom,room WHERE (room.Type=typeroom.Type) and (room.Name =?);");
            prst.setInt(1, name);
            ResultSet rs = prst.executeQuery();
            while (rs.next())
            {
                price = (rs.getInt("price"));
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }
        return price;
    }

    public TableModel getAllRoom()
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery(" Select room.name , room.type , typeroom.price ,room.state"
                    + "  From room , typeroom where room.type = typeroom.type Group by name ;");
            rstm = new ResultSetTableModel(res, 1);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public List<Room> getRoomTypeName()
    {
        ConnectDB.connect();
        ListRoom = new ArrayList<Room>();
        try
        {
            prst = ConnectDB.getPrst("Select typeroom.type From typeroom");
            ResultSet rs = prst.executeQuery();
            while (rs.next())
            {
                Room = new Room();
                Room.setTyperoom(rs.getString("type"));
                ListRoom.add(Room);
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }
        return ListRoom;
    }

    public Room getRoomName(int name)
    {
        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("Select room.Type,room.State,typeroom.Price From room,typeroom where name =? and room.Type=typeroom.Type");
            prst.setInt(1, name);
            ResultSet rs = prst.executeQuery();
            while (rs.next())
            {
                Room = new Room();
                Room.setTyperoom(rs.getString("Type"));
                Room.setState(rs.getString("State"));
                Room.setCostroom(Integer.parseInt(rs.getString("Price")));
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }
        return Room;
    }

    public Room getInfomationOfRoom(int Name)
    {
        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("select type,state from room where name =?");
            prst.setInt(1, Name);
            ResultSet rs = prst.executeQuery();
            while (rs.next())
            {
                Room = new Room();
                Room.setTyperoom(rs.getString("type"));
                Room.setState(rs.getString("state"));
                Room.setName(Name);
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }
        return Room;
    }

    public boolean TestInforCustomer(int Name) throws SQLException
    {
        ConnectDB.connect();
        String state = "";
        prst = ConnectDB.getPrst("select type,state from room,reservation where (Name =?) and(reservation.Room=?) and(room.Name=reservation.Room)");
        prst.setInt(1, Name);
        prst.setInt(2, Name);
        ResultSet rs = prst.executeQuery();
        while (rs.next())
        {
            state = rs.getString("state");
        }
        ConnectDB.close();
        if (state.equals("Đã thuê"))
        {
            return false;
        }
        else
        {
            return true;
        }
    }

    public boolean editInformationOfRoom(Room Room)
    {

        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("UPDATE room SET State=?,Type=? WHERE Name=?");
            prst.setString(1, Room.getState());
            prst.setString(2, Room.getTyperoom());
            prst.setInt(3, Room.getName());
            if (prst.executeUpdate() > 0)
            {
                return true;
            }

        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }
        return false;
    }

    public int getPriceOf(String type)
    {
        ConnectDB.connect();
        int price = 0;
        try
        {
            prst = ConnectDB.getPrst("SELECT Price FROM typeroom WHERE (Type=?);");
            prst.setString(1, type);
            ResultSet rs = prst.executeQuery();
            while (rs.next())
            {
                price = (rs.getInt("price"));
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }
        return price;
    }

    public Room getTyperoom(String type)
    {
        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("Select typeroom.Price From typeroom where Type=? ");
            prst.setString(1, type);
            ResultSet rs = prst.executeQuery();
            while (rs.next())
            {
                Room = new Room();
                Room.setCostroom(rs.getInt("Price"));
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }
        return Room;
    }

    public boolean editTypeRoom(Room Room)
    {
        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("UPDATE typeroom SET Price=? WHERE Type=?");
            prst.setString(1, Integer.toString(Room.getCostroom()));
            prst.setString(2, Room.getTyperoom());
            if (prst.executeUpdate() > 0)
            {
                return true;
            }

        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }
        return false;
    }

    public boolean deleteRoom(int name)
    {
        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("DELETE FROM room WHERE name=? ");
            prst.setInt(1, name);
            if (prst.executeUpdate() > 0)
            {
                return true;
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }

        return false;
    }

    public ResultSetTableModel filterNameRoom(String key)
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("select room.Name,room.Type,typeroom.Price,room.State From room,typeroom where (typeroom.Type=room.Type) and (`Name` like '%" + key + "%')");
            rstm = new ResultSetTableModel(res, 1);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public ResultSetTableModel filterTypeRoom(String key)
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("select room.Name,room.Type,typeroom.Price,room.State From room,typeroom where (typeroom.Type=room.Type) and (room.`Type` like '%" + key + "%')");
            rstm = new ResultSetTableModel(res, 1);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public ResultSetTableModel filterStateRoom(String key)
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("select room.Name,room.Type,typeroom.Price,room.State From room,typeroom where (typeroom.Type=room.Type) and (room.`State` like '%" + key + "%')");
            rstm = new ResultSetTableModel(res, 1);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public boolean TestInforRoom(int Name) throws SQLException
    {
        ConnectDB.connect();
        String Type = "";
        prst = ConnectDB.getPrst("select Room.Type from Room where Name=?");
        prst.setInt(1, Name);
        ResultSet rs = prst.executeQuery();
        while (rs.next())
        {
            Type = rs.getString("Type");
        }
        ConnectDB.close();

        if (Type != "")
        {
            return false;
        }
        else
        {
            return true;
        }
    }
}
